﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using EFITransmissao.Entidade;
using Dados.ConexaoCE;
using System.Data;
using System.Collections;
using Dados.ConexaoSQL;

namespace EFITransmissao.Dados.DAO
{
    public class DAOCadMedidor
    {
        public DAOCadMedidor() { }

        public static NovoMedidor ObterMedidorCadastro_UC(string uc)
        {
            NovoMedidor obj = new NovoMedidor();
            Hashtable prm = new Hashtable();
            prm["@uc"] = uc;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterMedidorCadastro_UC", prm);

                foreach (DataRow dr in dt.Rows)
                {
                    obj.Uc = dr["UC"].ToString();
                    obj.MedidorLocal = dr["MedidorLocal"].ToString();
                    obj.NovMedidor = dr["NovoMedidor"].ToString();
                    obj.Matricula = dr["Matricula"].ToString();
                    obj.Leiturista = dr["Leiturista"].ToString();
                    obj.Rota = dr["Rota"].ToString();
                    obj.Endereco = dr["Endereco"].ToString();
                    obj.Marca = dr["Marca"].ToString();
                    obj.Modelo = dr["Modelo"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return obj;
        }

        public static NovoMedidor ObterMedidorCadastro_MEDIDOR(string medidor)
        {
            NovoMedidor obj = new NovoMedidor();
            Hashtable prm = new Hashtable();
            prm["@medidor"] = medidor;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterMedidorCadastro_MEDIDOR", prm);

                foreach (DataRow dr in dt.Rows)
                {
                    obj.Uc = dr["UC"].ToString();
                    obj.MedidorLocal = dr["MedidorLocal"].ToString();
                    obj.NovMedidor = dr["NovoMedidor"].ToString();                    
                    obj.Matricula = dr["Matricula"].ToString();
                    obj.Leiturista = dr["Leiturista"].ToString();
                    obj.Rota = dr["Rota"].ToString();
                    obj.Endereco = dr["Endereco"].ToString();
                    obj.Marca = dr["Marca"].ToString();
                    obj.Modelo = dr["Modelo"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return obj;
        }

        public static List<string> ObterListaUC()
        {
            List<string> lst = new List<string>();
            
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterListaUC", null);

                lst.Add(string.Empty);
                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["UC"].ToString());                    
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }

        public static List<string> ObterListaMEDIDOR()
        {
            List<string> lst = new List<string>();

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterListaMEDIDOR", null);

                lst.Add(string.Empty);
                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["MedidorLocal"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }

        public static List<string> ObterListaMARCA()
        {
            List<string> lst = new List<string>();

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterListaMARCA", null);

                lst.Add(string.Empty);
                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["descricao"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }

        public static List<string> ObterListaMODELO()
        {
            List<string> lst = new List<string>();

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\efi_sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterListaMODELO", null);

                lst.Add(string.Empty);
                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["descricao"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }

        public static void AtualizaCadMedidor(NovoMedidor obj)
        {
            Hashtable prm = new Hashtable();
            prm["@uc"] = obj.Uc;
            prm["@novoMedidor"] = obj.NovMedidor;
            prm["@marca"] = obj.Marca;
            prm["@modelo"] = obj.Modelo;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                db.ExecutaAtualizacao("UpdateCadMedidor", prm);


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static void ExcluirCadMedidor(NovoMedidor obj)
        {
            Hashtable prm = new Hashtable();
            prm["@uc"] = obj.Uc;
            
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                db.ExecutaAtualizacao("DeleteCadMedidor", prm);


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static void ExcluirTodosCadMedidor()
        {
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                db.ExecutaAtualizacao("DeleteTodosCadMedidor", null);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static List<NovoMedidor> ObterTodosCadastrosNovoMedidor()
        {
            List<NovoMedidor> lst = new List<NovoMedidor>();

            string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

            CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
            DataTable dt = db.ExecutaConsulta("ObterTodosCadastrosMedidorCadastro", null);

            foreach (DataRow dr in dt.Rows)
            {
                NovoMedidor obj = new NovoMedidor();
                obj.Uc = dr["UC"].ToString();
                obj.MesFat = dr["MesFat"].ToString();
                obj.MedidorLocal = dr["MedidorLocal"].ToString();
                obj.NovMedidor = dr["NovoMedidor"].ToString();
                obj.Marca = dr["Marca"].ToString();
                obj.Modelo = dr["Modelo"].ToString();
                obj.Matricula = dr["Matricula"].ToString();
                obj.Leiturista = dr["Leiturista"].ToString();
                obj.Rota = dr["Rota"].ToString();
                obj.IdFoto = dr["IdFoto"].ToString();
                obj.Foto = (byte[])dr["Foto"];

                lst.Add(obj);
            }

            return lst;
        }

        public static void InsereListaMarcaCE()
        {
            List<string> lst = new List<string>();

            try
            {
                lst = getListaMarca();
                if (lst.Count > 1)
                {
                    string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                    CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                    
                    //Remove:
                    db.ExecutaAtualizacao("DeleteListaMarcasCE", null);

                    //Adiciona:
                    lst.Add(string.Empty);
                    foreach (var item in lst)
                    {
                        Hashtable prm = new Hashtable();
                        prm["@marca"] = item.ToString();
                                                
                        db.ExecutaAtualizacao("InsereMarcasCE", prm);

                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static void InsereListaModeloCE()
        {
            List<string> lst = new List<string>();

            try
            {
                lst = getListaModelo();
                if (lst.Count > 1)
                {
                    string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                    CEMSDataAccess db = new CEMSDataAccess(_stringconexao);

                    //Remove:
                    db.ExecutaAtualizacao("DeleteListaModeloCE", null);

                    //Adiciona:
                    lst.Add(string.Empty);
                    foreach (var item in lst)
                    {
                        Hashtable prm = new Hashtable();
                        prm["@modelo"] = item.ToString();

                        db.ExecutaAtualizacao("InsereModeloCE", prm);

                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        #region SQL Server

        public static void InserindoNovoMedidor()
        {
            //Busca todos os dados cadastrados.
            List<NovoMedidor> lst = new List<NovoMedidor>();
            lst = ObterTodosCadastrosNovoMedidor();

            foreach (var item in lst)
            {
                NovoMedidor obj = new NovoMedidor();
                obj.Uc = item.Uc;
                obj.MedidorLocal = item.MedidorLocal;
                obj.NovMedidor = item.NovMedidor;
                obj.Matricula = item.Matricula;
                obj.Leiturista = item.Leiturista;
                obj.Rota = item.Rota;
                obj.MesFat = item.MesFat;
                obj.IdFoto = item.IdFoto;                
                obj.Foto = item.Foto;
                obj.Marca = item.Marca;
                obj.Modelo = item.Modelo;

                MSSQLDataAccess db = new MSSQLDataAccess();
                db.ExecutaAtualizacaoNovoMedidor("InsereCadastroNovoMedidor", obj);
            }

            return;
        }

        private static List<string> getListaMarca()
        {
            List<string> lst = new List<string>();

            try
            {
                MSSQLDataAccess db = new MSSQLDataAccess();
                DataTable dt = db.ExecutaConsulta("ObterListaMarca", null);
                                
                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["descricao"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }

        private static List<string> getListaModelo()
        {
            List<string> lst = new List<string>();

            try
            {
                MSSQLDataAccess db = new MSSQLDataAccess();
                DataTable dt = db.ExecutaConsulta("ObterListaModelo", null);

                foreach (DataRow dr in dt.Rows)
                {
                    lst.Add(dr["descricao"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return lst;
        }
        
        #endregion
    }
}
